package com.bwda.dsrs.filecenter.service.impl;

import com.bwda.dsrs.base.util.AesEncryptPaddingUtil;
import com.bwda.dsrs.base.util.StringUtil;
import com.bwda.dsrs.filecenter.domain.po.dbinfo.DbInfoPo;
import com.bwda.dsrs.filecenter.domain.po.dbscanresult.DbScanResultPo;
import com.bwda.dsrs.filecenter.service.IRemoteService;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;


import java.sql.*;
import java.util.Date;
import java.util.*;
/**
 * @author Administrator
 */
@Service
public class MysqlRemoteServiceImpl implements IRemoteService {

    @Value("${aes_key}")
    private String aesKey;
    @Value("${mysql_builtIn_user}")
    private String mysqlBuiltInUser;

    private final Logger logger = LoggerFactory.getLogger(MysqlRemoteServiceImpl.class);
    @Override
    public Connection login(DbInfoPo dbInfoPo) throws Exception {

        //验证是否填写了数据库名称

        String serviceName="sys";
        if(StringUtil.isNotEmpty(dbInfoPo.getNeedScanDb())){
            serviceName=dbInfoPo.getNeedScanDb();
        }else{
            List<Object> listDb = getDataBases(dbInfoPo);
            if(!CollectionUtils.isEmpty(listDb)){
                serviceName=listDb.get(0).toString();
            }
        }
        StringBuffer url = new StringBuffer();
        url.append("jdbc:mysql://").append(dbInfoPo.getDbIp());
        url.append(":").append(dbInfoPo.getDbPort());
        url.append("/").append(serviceName);
        url.append("?user=").append(dbInfoPo.getDbAccount());
        String pwdDecryp = AesEncryptPaddingUtil.decrypt(dbInfoPo.getDbPassword(),aesKey);
        logger.info("ip:{},name:{}",dbInfoPo.getDbIp(),dbInfoPo.getServiceName());
        url.append("&password=").append(pwdDecryp);
        url.append("&useUnicode=true&characterEncoding=8859_1");
        url.append("&zeroDateTimeBehavior=CONVERT_TO_NULL");
        String driverName = "com.mysql.jdbc.Driver";
        Class.forName(driverName);
        DriverManager.setLoginTimeout(10);
        return DriverManager.getConnection(url.toString());
    }
    @Override
    public List<Map<String,Object>> getData(Connection connection,String tableName,String columnName,Integer rowNum,Integer sumRowNum) throws Exception {
        String sql;
        if(StringUtils.isBlank(columnName)){
            if(rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0){
//            sql = "select "+"`"+columnName+"`"+" from "+tableName+" limit "+rowNum+" offset "+(sumRowNum-rowNum);//最新数据
                sql = "select * from "+tableName+" limit "+rowNum;
            }
            else {
                sql = "select * from "+tableName;
            }
        }
        else{
            if(rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0){
//            sql = "select "+"`"+columnName+"`"+" from "+tableName+" limit "+rowNum+" offset "+(sumRowNum-rowNum);//最新数据
                sql = "select "+"`"+columnName+"`"+" from "+tableName+" limit "+rowNum;
            }
            else {
                sql = "select "+"`"+columnName+"`"+" from "+tableName;
            }
        }
        logger.info("getData will run sql = {}",sql);
//        List<Object> res = new ArrayList<>();
        List<Map<String,Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs=  stmt.executeQuery(sql);
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
        while (rs.next()) {
            Map<String,Object> rowData = new HashMap<>();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i).toLowerCase(), rs.getObject(i));//获取键名及值
            }
            res.add(rowData);
        }
//        while (rs.next()) {
//            for (int i = 1; i <= columnCount; i++) {
//                res.add( rs.getObject(i));
//            }
//        }
        rs.close();
        stmt.close();
        return res;
//        return JSONObject.parseArray(JSON.toJSONString(res),DbInfoPo.class);
    }
    @Override
    public List<String> getAllDBList(Connection connection,DbInfoPo dbInfoPo)throws Exception{
        ResultSet rs = connection.getMetaData().getSchemas();
        List<String> stringList = new ArrayList<>();
        while (rs.next()) {
            String tableSchem = rs.getString("TABLE_SCHEM");
            stringList.add(tableSchem);
            System.out.println(tableSchem);
        }
        rs.close();
        return stringList;
    }

    @Override
    public List<String> getAllTableList(Connection connection,String dbName) throws Exception{
       /* String[] types = { "TABLE" };
        ResultSet rs = connection.getMetaData().getTables(null,null, "%", types);

        //获取配置文件中oracle自带的用户
        List<String> adminList = new ArrayList<>();
        String[] sourceStrArray = mysqlBuiltInUser.split(",");
        for (int i = 0; i < sourceStrArray.length; i++) {
            adminList.add(sourceStrArray[i].trim());
        }

        List<String> stringList = new ArrayList<>();
        while (rs.next()) {
            //库名
            String uesrName =  rs.getString("TABLE_CAT");
            if(!adminList.stream().anyMatch(s -> StringUtils.endsWithIgnoreCase(s, uesrName))){
                // 表名
                String tableName = rs.getString("TABLE_NAME").toLowerCase();
                if (!tableName.contains("$")) {
                    stringList.add(uesrName.concat(".").concat(tableName));
                }

            }

        }*/
        List<String> stringList = new ArrayList<>();
        String querySql = "SELECT TABLE_NAME FROM \n" +
                           "information_schema.TABLES WHERE table_schema = '"+dbName+"'";
        Statement state = connection.createStatement();
        ResultSet rs = state.executeQuery(querySql);
        while(rs.next()){
            String tableName = rs.getString("TABLE_NAME").toLowerCase();
            stringList.add(dbName.concat(".").concat(tableName));
        }
        state.close();
        rs.close();

        return stringList;
    }

    @Override
    public List<DbScanResultPo> getTableColumns(Connection connection,String tableName) throws Exception{
        ResultSet rs = connection.getMetaData().getColumns(tableName.split("\\.")[0], tableName.split("\\.")[0], tableName.split("\\.")[1], "%");
        List<DbScanResultPo> dbScanResultPos = new ArrayList<>();
        while (rs.next()) {
            DbScanResultPo dbScanResultPo = new DbScanResultPo();
//        dbScanResultPo.setScanResultId(null);
            dbScanResultPo.setWordIds("");
            dbScanResultPo.setScanNums(0);
            dbScanResultPo.setMatchNums(0);
            dbScanResultPo.setCreateTime(new Date());
            dbScanResultPo.setUpdateTime(new Date());
            dbScanResultPo.setRemark("");
            dbScanResultPo.setDelFlag(0);
//            dbScanResultPo.setTableRemark(rs.getString("TABLE_SCHEM"));
            dbScanResultPo.setTableName(tableName.toLowerCase());
            dbScanResultPo.setColumnRemark(StringUtils.isEmpty(rs.getString("REMARKS"))?"":rs.getString("REMARKS").toLowerCase());
            dbScanResultPo.setColumnName(rs.getString("COLUMN_NAME").toLowerCase());
            dbScanResultPo.setColumnType(rs.getString("TYPE_NAME").toLowerCase());
            dbScanResultPos.add(dbScanResultPo);
//            String tableSchemaName = rs.getString("TABLE_SCHEM");// 表的架构（可能为空）
//            String tableName_ = rs.getString("TABLE_NAME");// 表名
//            String columnName = rs.getString("COLUMN_NAME");// 列名
//            System.err.println(tableSchemaName+'-'+tableName+'-'+columnName);
        }
        rs.close();
        return dbScanResultPos;
    }

    @Override
    public Integer countRowNum(Connection connection, String tableName) throws Exception {
        String sql = "select count(1) as rowcount from "+tableName;
        logger.info("countRowNum will run sql = {}",sql);
        List<Map<String,Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs=  stmt.executeQuery(sql);
        int count = 0;
        while (rs.next()){
            count = rs.getInt(1);
        }
//        int count = ((Long) res.get(0).get("rowcount")).intValue();
        rs.close();
        stmt.close();
        return count;
    }
    @Override
    public Integer countTable(Connection connection,String dbName) throws Exception{
        String[] types = { "TABLE" };
        ResultSet rs = connection.getMetaData().getTables(null,null, "%", types);
        int count = ((ResultSetImpl) rs).getRows().size();
        rs.close();
        return count;
    }
    @Override
    public Integer countView(Connection connection) throws Exception{
        String[] types = { "VIEW" };
        ResultSet rs = connection.getMetaData().getTables(null,null, "%", types);
        int count = ((ResultSetImpl) rs).getRows().size();
        rs.close();
        return count;
    }
    @Override
    public Integer countFunction(Connection connection) throws Exception{
        ResultSet rs = connection.getMetaData().getFunctions(null,null, "%");
        int count = ((ResultSetImpl) rs).getRows().size();
        rs.close();
        return count;
    }

    @Override
    public Integer countSequence(Connection connection) throws Exception {
        return null;
    }

    /**
     * 查询所有的库信息
     * @param dbInfoPo
     * @return
     */
    @Override
    public List<Object> getDataBases(DbInfoPo dbInfoPo) throws Exception{

        List<Object> list = new ArrayList();
        String url =("jdbc:mysql://"+dbInfoPo.getDbIp());
        String user=dbInfoPo.getDbAccount();
        String password=AesEncryptPaddingUtil.decrypt(dbInfoPo.getDbPassword(),aesKey);
        Connection con=null;
        ResultSet resultSet=null;
        try {
            DriverManager.setLoginTimeout(10);
            con=DriverManager.getConnection(url, user, password);
            DatabaseMetaData meta = con.getMetaData();
            resultSet = meta.getCatalogs();
            while(resultSet.next()){
                String db = resultSet.getString("TABLE_CAT");
                list.add(db);
            }
//            resultSet.close();
//            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(resultSet!=null && !resultSet.isClosed()){
                resultSet.close();
            }
            if(con!=null && !con.isClosed()){
                con.close();
            }
        }
        return list;
    }


}
